import pymysql.cursors
import pg8000.native
import psycopg2
import datetime


def mysql_connection(host: str, user: str, password: str, database: str):
    connection = pymysql.connect(host=host, user=user, password=password, database=database,
                                 cursorclass=pymysql.cursors.DictCursor)
    return connection


def mysql_cursor(connection):
    return connection.cursor()


def pg_connection(host: str, user: str, password: str, database: str):
    connection = psycopg2.connect(host=host, user=user, password=password, database=database, port=8000)
    return connection


def pg_cursor(connection):
    c = connection.cursor()
    c.execute("SET SESSION search_path='%s';" % 'payment_trade')
    return c


def append_order_bill_sql(begin: int, length: int):
    now = datetime.datetime.now()
    time = now.strftime("%Y-%m-%d %H:%M:%S")
    date = now.strftime("%Y%m%d")
    insert_sql = "INSERT INTO t_trade_orderbill (c_pkid, c_create_date, c_modify_date, c_amt00, c_callback_url, c_center_code, c_channel_code, c_paychannel, c_channel_name, c_channel_scene, c_trans_type, c_goodsname, c_mer_id, c_ext_column1, c_ext_column10, c_ext_column2, c_ext_column3, c_ext_column4, c_ext_column5, c_ext_column6, c_ext_column7, c_ext_column8, c_ext_column9, c_func_id, c_healthcardid, c_payee_account, c_payee_name, c_mobile, c_str3, c_operator_id, c_str1, c_str0, c_str2, c_orderno, c_pay_date, c_pay_name, c_pay_account, c_amt05, c_refund_count, c_refund_reason, c_scene_name, c_sign_code, c_status, c_sub_merchant_code, c_goodssubject, c_terminal_id, c_platno, c_type, c_yibao_flag) \n" \
                 "VALUES \n"
    values_template = "('%s', '%s', '%s', 0.01, 'http://test.com.cn/callback', null, 'weChat', 3, '微信', 'qrCode', 3, '挂号单据', 'd202408130001', null, null, null, null, null, null, null, null, null, null, 'P7001', '20240813', '109900020001', '上海市东方医院南院', null, null, '收费员01', null, null, null, '%s', '2024-08-13 09:30:55', '测试姓名', null, 0.01, 1, null, '线下主扫', null, 1, null, '挂号单据', null, null, 1, null)\n"
    for i in range(0, length):
        order_no = '{:012d}'.format(begin+i)
        insert_sql += values_template % ('ppo'+date+order_no, time, time, order_no)
        if i != length-1:
            insert_sql += ','
    insert_sql += ';'
    return insert_sql


def append_order_bill(connection, cursor, out_length: int, inner_length: int):
    with cursor:
        for i in range(0, out_length):
            sql = "select next_val from t_hibernate_sequences where sequence_name = 't_trade_orderbill'"
            cursor.execute(sql)
            result = cursor.fetchone()

            begin = result['next_val']
            sql = append_order_bill_sql(begin, inner_length)
            print(sql)
            cursor.execute(sql)
            update_sql = "update t_hibernate_sequences set next_val=next_val+%d where sequence_name = 't_trade_orderbill';"%(inner_length)
            print(update_sql)
            cursor.execute(update_sql)
    connection.commit()


if __name__ == "__main__":
    host = '10.17.73.80'
    user = 'root'
    password = 'oQCF7jJ8KR43U'
    database = 'payment_trade'
    connection = mysql_connection(host, user, password, database)
    # host = '10.17.73.49'
    # user = 'payment'
    # password = 'Bsoft@123'
    # database = 'payment'
    # connection = pg_connection(host, user, password, database)

    cursor = mysql_cursor(connection)
    append_order_bill(connection, cursor, 500, 1000)
    connection.commit()
    connection.close()

